\c demo_tsearch postgres
\set ON_ERROR_STOP on

/**** NOTE: all files must be UTF8, otherwise it cannot be read *******/

-- create a custom text search configuration
drop text search configuration if exists english_ispell cascade;
create text search configuration english_ispell (copy=pg_catalog.simple);

-- create a thesaurus
drop text search dictionary if exists english_thesaurus;
create text search dictionary english_thesaurus (
    template = thesaurus,
    dictfile = english_thesaurus_demo,
    dictionary = english_stem
);

-- create a synonym dictionary
drop text search dictionary if exists english_synonym cascade;
create text search dictionary english_synonym (
    template = synonym,
    synonyms = english_synonym_demo
);

-- create a dictionary incorporating the english ISPELL dictionary
drop text search dictionary if exists english_ispell cascade;
create text search dictionary english_ispell (
    template  = ispell,
    dictfile  = english,
    afffile   = english,
    stopwords = english
);

-- combine all dictionaries in the text search configuration 'english_ispell'
--      note: dictionary english_stem already exists
alter text search configuration english_ispell
    alter mapping for asciiword, asciihword, hword_asciipart,
                      word, hword, hword_part
    with
         english_thesaurus,
	 english_synonym,
  	 english_ispell,
	 english_stem;

\H \o '04_ex1.html'
show default_text_search_config;

\dF
\dFd+ simple
\dFd+ english_ispell
\dF+ simple
\dF+ english_ispell
\dF+ english

\qecho '<hr><p><b>TEST DICTIONARIES WITH THE WORD "plowed": english_stem, simple, english_ispell ...</b>'
select * from ts_lexize('english_stem', 'plowed')
union
select * from ts_lexize('simple', 'plowed')
union
select * from ts_lexize('english_ispell', 'plowed');


\qecho '<hr><p><b>TEST A CONFIGURATION ...</b>'
select * from ts_debug('english', 'plowed');
select * from ts_debug('simple', 'plowed');
select * from ts_debug('english_ispell', 'plowed');

select * from ts_debug('english', 'postgresql');
select * from ts_debug('simple', 'postgresql');
select * from ts_debug('english_ispell', 'postgresql');

select * from ts_debug('english', 'emerald city');
select * from ts_debug('simple', 'emerald city');
select * from ts_debug('english_ispell', 'emerald city');

\qecho '<hr><p><b>CONVERT STRING INTO A TEXT SEARCH VECTOR</b></p>'
\qecho <p>select to_tsvector('english_ispell','GIS relational database management system plowed blower pillow')
\qecho <p>select to_tsvector('english_ispell','emerald city database management system');
\qecho <p>select to_tsvector('english_ispell','postgresql POSTGRES');
\qecho <p>select to_tsvector('english_ispell','emerald city');

select to_tsvector('english_ispell','GIS relational database management system plowed blower pillow')
union
select to_tsvector('english_ispell','emerald city database management system')
union
select to_tsvector('english_ispell','postgresql POSTGRES')
union
select to_tsvector('english_ispell','emerald city');
